*This file produces the results for Table 1, where we calculate summary statistics on different subsets of our analysis sample.

********************************************************************************
*DEFINE DIRECTORIES
local home CHILD
local main CHILD/JPE
local logs CHILD/JPE/logs
local data CHILD/JPE/data
local results CHILD/JPE/results
local network NETWORK
********************************************************************************

*bring in data
cd "`home'"
use basis_sample.dta, clear

*merge in demographics
merge 1:1 patientid using demographics_check.dta
keep if _merge==3
drop _merge

*merge in costs
merge 1:1 patientid using comp_costs.dta
drop if _merge==2
drop _merge

*merge in facility
merge 1:1 patientid using comp_fac.dta
drop if _merge==2
drop _merge

*merge in coverage dates
preserve
import delimited comparison_carveout_check2.txt, varnames(1) clear
keep patientid earliest latest
tempfile temp
save `temp', replace
restore

merge 1:1 patientid using `temp'
keep if _merge==3
drop _merge

generate early = date(earliest,"YMD")
generate late = date(latest,"YMD")
format early late %td
drop earliest latest

*recode zeros
foreach var of varlist costsfacility-count_h {
replace `var' =0 if `var'==.
}

*generate coverage months
generate months = mofd(late)-mofd(early) + 1

*generate monthly averages
generate avg_costs = total/months
generate hospital = count_h/months
generate er = count_e/months

*generate age at first insurer appearance
generate age_at_begin = floor( (mofd(early)-mofd(birthday))/12)

*merge in neurodevelopmental+self_harm diagnoses
merge 1:1 patientid using diagnoses_insurer_population.dta
drop _merge

*recode zeros for anyone who doesn't appear
foreach var of varlist nd-adjustment_dax {
    replace `var' = 0 if `var'==.
}

*parse variables
keep patientid in_sample female age_at_begin hospital er avg_costs therapy_only drug_only both birthday nd-adjustment_dax

*create flag for JPE sample (follow 2 years) ***NOTE*** This is new JPE flag.
cd "`data'"
merge 1:1 patientid using final_jpe_sample.dta
generate jpe_new = _merge==3
drop _merge

save all_summary_statistics.dta, replace

***********STATS START HERE

cap log close
cd "`results'"
log using table1.txt, text replace

cd "`data'"
use all_summary_statistics.dta, clear
tempfile temp
save `temp', replace

clear
generate id = .
tempfile table
save `table', replace

local outcomes female age_at_begin hospital self_harm er avg_costs nd adhd

use `temp', clear
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "All Insurer"
generate id = 1
append using `table'
save `table', replace

use `temp', clear
keep if in_sample
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "All MH INSURER"
generate id = 2
append using `table'
save `table', replace

use `temp', clear
keep if jpe_new
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "Sample"
generate id = 3
append using `table'
save `table', replace

use `temp', clear
keep if treated & jpe_new
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "Treated"
generate id = 4
append using `table'
save `table', replace

use `temp', clear
keep if therapy_only & jpe_new
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "Therapy Only"
generate id = 5
append using `table'
save `table', replace

use `temp', clear
keep if both & jpe_new
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "Therapy and Drugs"
generate id = 6
append using `table'
save `table', replace

use `temp', clear
keep if (drug_only | both) & jpe_new
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "Drugs"
generate id = 7
append using `table'
save `table', replace

use `temp', clear
keep if fda_ok & jpe_new
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "FDA-Approved Drugs"
generate id = 8
append using `table'
save `table', replace

use `temp', clear
keep if grey_area & jpe_new
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "Grey-Area Drugs"
generate id = 9
append using `table'
save `table', replace

use `temp', clear
keep if red_flag & jpe_new
generate count = 1
collapse (mean) `outcomes' (sum) count
generate group = "Red-Flag Drugs"
generate id = 10
append using `table'
save `table', replace

**********************************now, study the MH statistics
use `temp', clear
keep if in_sample==1

cd "`home'"
*drop zcta_kid

preserve
use regression_sample.dta, clear
keep patientid first_hosp first_er first_eval first_mh
tempfile merge
save `merge', replace
restore

merge 1:1 patientid using `merge', update
drop if _merge==2
drop _merge

generate age = floor((mofd(first_mh)-mofd(birthday))/12)
tempfile temp2
save `temp2', replace


clear
generate id = .
tempfile table2
save `table2', replace

local outcomes age first_hosp first_er first_eval depression-adjustment_dax

use `temp2', clear
keep if in_sample
generate count = 1
collapse (mean) `outcomes'
generate group = "All MH Insurer"
generate id = 2
append using `table2'
save `table2', replace

use `temp2', clear
keep if jpe_new
generate count = 1
collapse (mean) `outcomes'
generate group = "Sample"
generate id = 3
append using `table2'
save `table2', replace

use `temp2', clear
keep if treated & jpe_new
generate count = 1
collapse (mean) `outcomes'
generate group = "Treated"
generate id = 4
append using `table2'
save `table2', replace

use `temp2', clear
keep if therapy_only & jpe_new
generate count = 1
collapse (mean) `outcomes'
generate group = "Therapy Only"
generate id = 5
append using `table2'
save `table2', replace

use `temp2', clear
keep if both & jpe_new
generate count = 1
collapse (mean) `outcomes'
generate group = "Therapy and Drugs"
generate id = 6
append using `table2'
save `table2', replace

use `temp2', clear
keep if (drug_only | both) & jpe_new
generate count = 1
collapse (mean) `outcomes'
generate group = "Drugs"
generate id = 7
append using `table2'
save `table2', replace

use `temp2', clear
keep if fda_ok & jpe_new
generate count = 1
collapse (mean) `outcomes'
generate group = "FDA-Approved Drugs"
generate id = 8
append using `table2'
save `table2', replace

use `temp2', clear
keep if grey_area & jpe_new
generate count = 1
collapse (mean) `outcomes'
generate group = "Grey-Area Drugs"
generate id = 9
append using `table2'
save `table2', replace

use `temp2', clear
keep if red_flag & jpe_new
generate count = 1
collapse (mean) `outcomes'
generate group = "Red-Flag Drugs"
generate id = 10
append using `table2'
save `table2', replace

************************************combine both panels
use `table', clear
merge 1:1 id using `table2'
drop _merge
sort id
order group count, first
drop id

cd "`results'"
export excel using "table1.xlsx", firstrow(variables) replace
